package uk.ac.sanger.das.dasregistry.springrest.sources;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.List;
import java.util.logging.Logger;

import org.biodas.jdas.dassources.Capabilities;


/*
 *                  BioJava development code
 *
 * This code may be freely distributed and modified under the
 * terms of the GNU Lesser General Public Licence.  This should
 * be distributed with the code.  If you do not have a copy,
 * see:
 *
 *      http://www.gnu.org/copyleft/lesser.html
 *
 * Copyright for this code is held jointly by the individual
 * authors.  These should be listed in @author doc comments.
 *
 * For more information on the BioJava project and its aims,
 * or to join the biojava-l mailing list, visit the home page
 * at:
 *
 *      http://www.biojava.org/
 * */


/**
 * class to help DasSourceManager with generating sql queries- queries all in same place so we can easily see commonalities and ways to improve
 * @author jw12
 *
 */
public class DasSourceManagerSql {
	
	private String sqlStart="SELECT  distinct(r.auto_id),r.url,r.adminemail,r.description,r.registerDate, r.leaseDate, r.helperurl, r.testcode, r.nickname, r.prefix, r.alert_admin ";
	
	private String capabilitiesSql="";
	
	public static String OLD_BASIC_SELECT = "SELECT "
		+ " distinct(r.auto_id),r.url,r.adminemail,r.description,r.registerDate,"
		+ " r.leaseDate, r.helperurl, r.testcode, r.nickname, r.prefix, r.alert_admin "
		+ " from registry r";
	 private String sqlEnd=" from validated_capabilities v, registry r where r.auto_id=v.auto_id"+" and protocol != 2";

	public static Logger logger = Logger
	.getLogger("org.biojava.services.das.dao.DasSourceManagerSql");
	
	
	
	
	public  String getAllSourcesSql(){
		String cmd2 = this.getStatedAndValidatedSql();
		
	return sqlStart+cmd2+sqlEnd;
}
	
	
	/**
	 * get a string for sql queries that gets all capabilities from the Capabilities enum class
	 * saves us from having to write the long list of capabilities in the sql
	 * @return
	 */
	public String capabilitiesSql(){
		if(capabilitiesSql.equals("")){
		
		for(Capabilities cap: EnumSet.allOf(Capabilities.class)){
			capabilitiesSql+=cap.getCommand()+",";
		}
		capabilitiesSql=capabilitiesSql.substring(0,capabilitiesSql.length()-1);//chop off the last ,
		}else{
			return capabilitiesSql;
		}
		return capabilitiesSql;
	}
	
	/**
	 * helper method to get the stated and valid capabilities
	 * @return
	 */
	public  String getStatedAndValidatedSql() {
		String sql="";
		for(Capabilities cap: EnumSet.allOf(Capabilities.class)){
			sql+=", r."+cap.toString()+",v."+cap.toString()+" as v"+cap.toString();
		}
		return sql;
	}
	public String getStatedCapabiltiesSql(){

//		String sql = "SELECT sequence, structure, alignment, types, "
//				+ "features, entry_points, dna , stylesheet, interaction, sources from registry "
//				+ "where auto_id = ?";
		String sql="select "+this.capabilitiesSql()+ " from registry "
				+ "where auto_id =";
		return sql;

	}
	public String getValidCapabilitiesSql() {
//System.out.println("select "+this.capabilitiesSql()+" from validated_capabilities "
				//	+ " where auto_id = ");
			String sql = "select "+this.capabilitiesSql()+" from validated_capabilities "
					+ " where auto_id =";
			return sql;
	}
	
	public String findServiceSql(){
		//old query that works but calls many getValidCapabilities one for each id= SELECT DISTINCT(registry.auto_id),registry.url,registry.adminemail,   registry.description,registry.registerDate,registry.leaseDate,helperurl,testcode,nickname,registry.prefix,alert_admin  from    registry2coordNS, registry,coordNS,registry2label  where registry.auto_id = registry2coordNS.auto_id and    registry2coordNS.coordNS_auto= coordNS.coordNS_auto and (  ( coordNS.ncbi_tax_id = 9606)  ) 
		//other query for all sources looke like this= SELECT  distinct(r.auto_id),r.url,r.adminemail,r.description,r.registerDate, r.leaseDate, r.helperurl, r.testcode, r.nickname, r.prefix, r.alert_admin, r.sources,v.sources as vsources, r.stylesheet,v.stylesheet as vstylesheet, r.features,v.features as vfeatures, r.types,v.types as vtypes, r.sequence,v.sequence as vsequence, r.entry_points,v.entry_points as ventry_points, r.alignment,v.alignment as valignment, r.structure,v.structure as vstructure, r.interaction,v.interaction as vinteraction, r.error_segment,v.error_segment as verror_segment, r.unknown_segment,v.unknown_segment as vunknown_segment, r.unknown_feature,v.unknown_feature as vunknown_feature, r.feature_by_id,v.feature_by_id as vfeature_by_id from validated_capabilities v, registry r where r.auto_id=v.auto_id and protocol != 2
//successful with validcapabilities new added SELECT DISTINCT(r.auto_id),r.url,r.adminemail,   r.description,r.registerDate,r.leaseDate,r.helperurl,r.testcode,r.nickname,r.prefix,r.alert_admin , r.sources,v.sources as vsources, r.stylesheet,v.stylesheet as vstylesheet, r.features,v.features as vfeatures, r.types,v.types as vtypes, r.sequence,v.sequence as vsequence, r.entry_points,v.entry_points as ventry_points, r.alignment,v.alignment as valignment, r.structure,v.structure as vstructure, r.interaction,v.interaction as vinteraction, r.error_segment,v.error_segment as verror_segment, r.unknown_segment,v.unknown_segment as vunknown_segment, r.unknown_feature,v.unknown_feature as vunknown_feature, r.feature_by_id,v.feature_by_id as vfeature_by_id from   registry r, validated_capabilities v, registry2coordNS, coordNS,registry2label  where r.auto_id=v.auto_id and protocol != 2 and r.auto_id = registry2coordNS.auto_id and    registry2coordNS.coordNS_auto= coordNS.coordNS_auto and (  ( coordNS.ncbi_tax_id = 9606)  ) 		
		System.out.println("running find service sql blah blah");
		//String sql ="SELECT DISTINCT(r.auto_id),r.url,r.adminemail,   r.description,r.registerDate,r.leaseDate,r.helperurl,r.testcode,r.nickname,r.prefix,r.alert_admin "
			//+getStatedAndValidatedSql()+
			//" from   registry r, validated_capabilities v, registry2coordNS, coordNS,registry2label  where r.auto_id=v.auto_id and protocol != 2 and r.auto_id = registry2coordNS.auto_id and    registry2coordNS.coordNS_auto= coordNS.coordNS_auto";
		
		
		
		
		String sql = "SELECT DISTINCT(registry.auto_id),registry.url,registry.adminemail,"
			+ "   registry.description,registry.registerDate,registry.leaseDate,helperurl,"
			+ "testcode,nickname,registry.prefix,alert_admin "
			+
			" from "
			+ "   registry2coordNS, registry,coordNS,registry2label, validated_capabilities "
			+ " where registry.auto_id = registry2coordNS.auto_id and "
			+ "   registry2coordNS.coordNS_auto= coordNS.coordNS_auto";

		return sql;
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		//  Auto-generated method stub

	}

}
